<?php
/**
 * tool
 */
namespace App\Http\Controllers\Tool;

use App\Http\Controllers\Controller;
use App\Models\Auth\Database\Permission;
use App\Models\Basics\SysPlatAccount;
use App\Models\Basics\SysCountry;
use App\Models\Basics\Supplier;
use App\Models\Basics\SysArea;
use App\Models\Basics\SysICountry;
use App\Models\MonthlyReport\Payment\PaymentFBAFee;
use App\Models\OverSeas\AdjustFNSku;
use App\Models\OverSeas\FBADeliveryDetail;
use App\Models\Delivery\OSDelivery;
use App\Models\Delivery\OSDeliveryBox;
use App\Models\Delivery\OSDeliveryBoxDetail;
use App\Models\Delivery\OSDeliveryDetail;
use App\Models\MonthlyReport\Cost\LogisticsBasicsT;
use App\Models\OverSeas\FnSkuTranLog;
use App\Models\OverSeas\InventoryFNSku;
use App\Models\OverSeas\RemovaleOrder;
use App\Models\OverSeas\Warehouse;
use App\Models\Product\ProductSku;
use App\Models\SalesReport\DailySv;
use App\Models\SalesReport\PaymentArea;
use App\Models\SalesReport\PaymentCASummary;
use App\Util\RExcel;
use App\Util\User;
use Illuminate\Support\Facades\DB;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Tightenco\Collect\Support\Arr;

class ToolController extends Controller
{
    public function exec()
    {
        dd(111);
        ini_set('memory_limit','200M');
        set_time_limit(0);
        try{
            DB::beginTransaction();
            if($func = request()->get('func')){
                if($param = request()->get('p')){
                    $this->{$func}($param);
                }else{
                    $this->{$func}();
                }
            }else{
//                $this->exportInv();
            }
            DB::commit();
        }catch(\Exception $e){
            DB::rollBack();
            report($e);
        }
    }

    protected function exportInv(){
        $lines = DailySv::query()
            ->whereIn('country_id',[1,2,3,4])
            ->whereHas('sku', function ($query) {
            $query->whereIn('psku_code',['AM04182','AM04183','AM04184','AM04185']);
        })->orderBy('psku_id')->get();
        RExcel::exportCsv("库存.csv",function () use ($lines) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            $data = [];
            foreach ($lines as $row) {
                $data[$row->country_id][] = [$row->sku->psku_code ?? '',$row->country->country ?? '',$row->fba_instant_num,$row->os_instant_num];
            }
            foreach ($data as $rows) {
                $fbaInstant = 0;
                $osInstant = 0;
                foreach ($rows as $_row) {
                    $fbaInstant += $_row[2];
                    $osInstant += $_row[3];
                }
                fputcsv($fp, ['', '', $fbaInstant,$osInstant]);
                foreach ($rows as $_row) {
                    fputcsv($fp, [
                        $_row[0],
                        $_row[1],
                        $_row[2],
                        $_row[3],
                    ]);
                }
            }
            fclose($fp);
        });
    }

    protected static function _getInvData($re){
        foreach ($re as $row){
            $temp = [
                $row->sku->psku_code ?? '',
                $row->country->country ?? '',
                $row->fba_instant_num,
                $row->os_instant_num,
            ];
            yield $temp;
        }
    }

    protected function exportOsxx(){
        $head = ['发货单','仓库','账号','销售','发货时间','重复项'];
        $file = base_path('pp.xlsx');
        $lines = RExcel::importSheet($file);
        unset($lines[0]);
        dd($lines);
        RExcel::exportCsv("发货记录.csv",function () use ($head, $lines) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $noArr = [];
            foreach ($lines as $v){dd($v);
                $no = isset($v[0]) ? trim($v[0]) : '';
                if(!$no)continue;
                if(in_array($no,$noArr)){
                    $flag = 1;
                }else{
                    $noArr[] = $no;
                    $flag = 0;
                }
                $time = isset($v[4]) ? trim($v[4]) : '';
                $temp = [
                    $no,
                    isset($v[1]) ? trim($v[1]) : '',
                    isset($v[2]) ? trim($v[2]) : '',
                    isset($v[3]) ? trim($v[3]) : '',
                    $time ? date('Y-m-d H:i:s',strtotime($time)) : '',
                    $flag,
                ];
                fputcsv($fp, $temp);
            }
            fclose($fp);
        });
    }

    protected function exportOsTran(){
        $head = ['仓库','出账号','入账号','出FNSKU','入FNSKU','数量','操作人','创建时间'];
        $query = FnSkuTranLog::query();
        RExcel::exportCsv("换标记录.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(800, function ($re) use ($fp){
                $data = self::_getOsTranData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getOsTranData($re){
        $warehouse = [];
        $account = [];
        $user = [];
        foreach ($re as $row){
            if(!array_key_exists($row->warehouse_id,$warehouse)){
                $warehouse[$row->warehouse_id] = Warehouse::getCodeById($row->warehouse_id);
            }
            if(!array_key_exists($row->oaccount_id,$account)){
                $account[$row->oaccount_id] = SysPlatAccount::getAccountById($row->oaccount_id);
            }
            if(!array_key_exists($row->iaccount_id,$account)){
                $account[$row->iaccount_id] = SysPlatAccount::getAccountById($row->iaccount_id);
            }
            if(!array_key_exists($row->ouser_id,$user)){
                $user[$row->ouser_id] = User::getUsernameById($row->ouser_id);
            }
            $temp = [
                $warehouse[$row->warehouse_id],
                $account[$row->oaccount_id],
                $account[$row->iaccount_id],
                $row->ofnsku,
                $row->ifnsku,
                $row->transfer_num,
                $user[$row->ouser_id],
                $row->created_at,
            ];
            yield $temp;
        }
    }

    protected function exportOsAdjust(){
        $head = ['账号','仓库','SKU','品名','FNSKU','调整前数量','调整后数量','调整类型','操作人','创建时间'];
        $query = AdjustFNSku::query();
        RExcel::exportCsv("库存调整记录.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(800, function ($re) use ($fp){
                $data = self::_getOsAdjustData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getOsAdjustData($re){
        $account = [];
        $warehouse = [];
        $user = [];
        $sku = [];
        foreach ($re as $row){
            if(!array_key_exists($row->account_id,$account)){
                $account[$row->account_id] = SysPlatAccount::getAccountById($row->account_id);
            }
            if(!array_key_exists($row->warehouse_id,$warehouse)){
                $warehouse[$row->warehouse_id] = Warehouse::getCodeById($row->warehouse_id);
            }
            if(!array_key_exists($row->psku_id,$sku)){
                $sku[$row->psku_id] = ProductSku::getModelById($row->psku_id);
            }
            if(!array_key_exists($row->auser_id,$user)){
                $user[$row->auser_id] = User::getUsernameById($row->auser_id);
            }
            $skuModel = $sku[$row->psku_id];
            $temp = [
                $account[$row->account_id],
                $warehouse[$row->warehouse_id],
                $skuModel->psku_code ?? $row->psku_id,
                $skuModel->psku_cn ?? $row->psku_id,
                $row->fnsku,
                $row->b_num,
                $row->a_num,
                AdjustFNSku::$_type[$row->type],
                $user[$row->auser_id],
                $row->created_at,
            ];
            yield $temp;
        }
    }

    protected function exportFbaRemoval(){
        $head = ['订单号','账号','SKU','品名','FNSKU','发货数量','创建时间'];
        $query = RemovaleOrder::query()->where('status',2);
        RExcel::exportCsv("入仓移库已入库数据.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(800, function ($re) use ($fp){
                $data = self::_getOSInRemovalData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getOSInRemovalData($re){
        $account = [];
        $sku = [];
        foreach ($re as $row){
            if(!array_key_exists($row->account_id,$account)){
                $account[$row->account_id] = SysPlatAccount::getAccountById($row->account_id);
            }
            if(!array_key_exists($row->psku_id,$sku)){
                $sku[$row->psku_id] = ProductSku::getModelById($row->psku_id);
            }
            $skuModel = $sku[$row->psku_id];
            $temp = [
                $row->order_id,
                $account[$row->account_id],
                $skuModel->psku_code ?? $row->psku_id,
                $skuModel->psku_cn ?? $row->psku_id,
                $row->fnsku,
                $row->ship_num,
                $row->created_at,
            ];
            yield $temp;
        }
    }

    protected function exportFbaOsDetail(){
        $head = ['发货单','站点','仓库','账号','销售','SKU','品名','FNSKU','发货数量','未抵扣数量','发货时间','创建时间'];
        $query = FBADeliveryDetail::query()->with('delivery');
        RExcel::exportCsv("出仓发货单抵扣数据.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(800, function ($re) use ($fp){
                $data = self::_getOSOutDetailData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getOSOutDetailData($re){
        $account = [];
        $seller = [];
        $sku = [];
        foreach ($re as $row){
            $accountId = $row->delivery->account_id ?? 0;
            if(!array_key_exists($accountId,$account)){
                $account[$accountId] = SysPlatAccount::getAccountById($accountId);
            }
            $sellerId = $row->delivery->seller_id ?? 0;
            if(!array_key_exists($sellerId,$seller)){
                $seller[$sellerId] = User::getUsernameById($sellerId);
            }
            if(!array_key_exists($row->psku_id,$sku)){
                $sku[$row->psku_id] = ProductSku::getModelById($row->psku_id);
            }
            $skuModel = $sku[$row->psku_id];
            $temp = [
                $row->delivery_no,
                $row->delivery->area->area ?? '',
                $row->delivery->warehouse->w_code ?? '',
                $account[$accountId],
                $seller[$sellerId],
                $skuModel->psku_code ?? $row->psku_id,
                $skuModel->psku_cn ?? $row->psku_id,
                $row->fnsku,
                $row->ship_num,
                $row->back_num,
                $row->delivery->delivered_at ?? '',
                $row->created_at,
            ];
            yield $temp;
        }
    }

    protected function exportOsDetail(){
        $head = ['发货单','站点','仓库','账号','销售','SKU','品名','FNSKU','发货数量','未出库数量','当前在库数量(加账号)','当前在库数量(不加账号)','出库记录','发货时间','创建时间'];
        $query = OSDeliveryDetail::query()->with('delivery')->whereNotIn('delivery_no',['OSD20051500001','OSD20070300001']);
        RExcel::exportCsv("入仓发货单出库数据.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(800, function ($re) use ($fp){
                $data = self::_getOSInDetailData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getOSInDetailData($re){
        $account = [];
        $seller = [];
        $sku = [];
        $instant = [];
        $instantSum = [];
        foreach ($re as $row){
            if(!array_key_exists($row->account_id,$account)){
                $account[$row->account_id] = SysPlatAccount::getAccountById($row->account_id);
            }
            $sellerId = $row->delivery->seller_id ?? 0;
            if(!array_key_exists($sellerId,$seller)){
                $seller[$sellerId] = User::getUsernameById($sellerId);
            }
            if(!array_key_exists($row->psku_id,$sku)){
                $sku[$row->psku_id] = ProductSku::getModelById($row->psku_id);
            }
            $skuModel = $sku[$row->psku_id];
            $record = '';
            if($row->record){
                foreach ($row->record as $outOrder => $outNum){
                    $record .= $outOrder.'：'.$outNum.' ';
                }
            }
            $warehouseId = $row->delivery->warehouse_id ?? 0;
            $keyInstant = $warehouseId.'_'.$row->account_id.'_'.$row->fnsku;
            if(!array_key_exists($keyInstant,$instant)){
                $instant[$keyInstant] = InventoryFNSku::getInstant($warehouseId,$row->account_id,$row->fnsku);
            }
            $keyInstantSum = $warehouseId.'_'.$row->fnsku;
            if(!array_key_exists($keyInstantSum,$instantSum)){
                $instantSum[$keyInstantSum] = InventoryFNSku::query()
                    ->where('warehouse_id',$warehouseId)
                    ->where('fnsku',$row->fnsku)
                    ->sum('instant');
            }
            $temp = [
                $row->delivery_no,
                $row->delivery->area->area ?? '',
                $row->delivery->warehouse->w_code ?? '',
                $account[$row->account_id],
                $seller[$sellerId],
                $skuModel->psku_code ?? $row->psku_id,
                $skuModel->psku_cn ?? $row->psku_id,
                $row->fnsku,
                $row->ship_num,
                $row->back_num,
                $instant[$keyInstant],
                $instantSum[$keyInstantSum],
                $record,
                $row->delivery->delivered_at ?? '',
                $row->created_at,
            ];
            yield $temp;
        }
    }

    protected function exportBox(){
        $head = ['发货单','站点','SKU','品名','FNSKU','长','宽','高','重','每箱数量'];
        $query = OSDeliveryBoxDetail::query();
        RExcel::exportCsv("箱规.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(2000, function ($re) use ($fp){
                $data = self::_getBoxDetailData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getBoxDetailData($re){
        $area = [];
        $skuName = [];
        foreach ($re as $row){
            if(!array_key_exists($row->delivery_no,$area)){
                $areaId = OSDelivery::query()->where('delivery_no',$row->delivery_no)->value('area_id');
                $area[$row->delivery_no] = SysArea::getSysAreaById($areaId);
            }
            if(!array_key_exists($row->sku,$skuName)){
                $skuName[$row->sku] = ProductSku::getValueByCode($row->sku,'psku_cn');
            }
            $box = OSDeliveryBox::query()->find($row->delivery_box_id);
            $temp = [
                $row->delivery_no,
                $area[$row->delivery_no],
                $row->sku,
                $skuName[$row->sku],
                $row->fnsku,
                $box->long ?? 0,
                $box->wide ?? 0,
                $box->high ?? 0,
                $box->weight ?? 0,
                $row->num,
            ];
            yield $temp;
        }
    }

    protected function exportMs(){
        $head1 = ['站点'];
        $head2 = [];
        for($i = 1; $i <= 14; $i++){
            $head2[] = date('Y/m',strtotime("midnight first day of -$i month")).'销量';
            $head2[] = date('Y/m',strtotime("midnight first day of -$i month")).'销售额';
        }
        $head = array_merge($head1,$head2);
        $pas = PaymentArea::query()->where('report_date','>=','2020-09-01')->get();
        $data = [];
        foreach ($pas as $pa){
            $data[$pa->area_id][$pa->report_date] = [$pa->sales_volume,$pa->sales_amount_rmb];
        }
        RExcel::exportCsv("销售数据",function () use ($head, $data) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            foreach ($data as $areaId => $item) {
                $area = SysArea::getSysAreaById($areaId);
                $temp = [$area];
                for($i = 1; $i <= 14; $i++){
                    $date = date('Y-m-d',strtotime("midnight first day of -$i month"));
                    $temp[] = array_key_exists($date,$item) ? $item[$date][0] : 0;
                    $temp[] = array_key_exists($date,$item) ? $item[$date][1] : 0;
                }
                fputcsv($fp, $temp);
            }
            fclose($fp);
        });
    }

    protected function exportSupplier(){
        $head = ['编码', '名称', '详细地址'];
        $query = Supplier::query();
        RExcel::exportCsv("供应商.csv",function () use ($head, $query) {
            $fp = fopen('php://output', 'w');
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
            fputcsv($fp, $head);
            $query->orderBy('id')->chunk(100, function ($re) use ($fp){
                $data = self::_getSupplierData($re);
                foreach ($data as $item) {
                    fputcsv($fp, $item);
                }
            });
            fclose($fp);
        });
    }

    protected static function _getSupplierData($re){
        foreach ($re as $row){
            $temp = [
                $row->code,
                $row->name,
                $row->address,
            ];
            yield $temp;
        }
    }

    protected function upSupplier(){
        $file = base_path('供应商.xlsx');
        $lines = RExcel::importSheet($file);
        $errors = [];
        unset($lines[0]);
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 3)continue;
            $code = isset($v[0]) ? trim($v[0]) : '';
            $mp = isset($v[1])&&$v[1] ? trim($v[1]) : '';
            $status = isset($v[2])&&$v[2] ? trim($v[2]) : '';
            if(!$code || !$mp || !$status){
                $errors[$count] = '必填项为空';
                continue;
            }
            $supplier = Supplier::query()->where('code',$code)->first();
            if(!$supplier){
                $errors[$count] = '供应商ERROR';
                continue;
            }
            $supplier->main_product = $mp;
            $supplier->status = $status;
            $supplier->save();
        }
        if(!empty($errors)){
            RExcel::importResult($errors);
        }
    }

    protected function logisticsPrice(){
        $file = base_path('logicts.xlsx');
        $lines = RExcel::importSheet($file);
        $errors = [];
        unset($lines[0]);
        $countryArr = [];
        $areaArr = [];
        $skuArr = [];
        $arr = [];
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 4)continue;
            $bDate = isset($v[0]) ? date('Y-m-d',strtotime(trim($v[0]))) : '';
            $sku = isset($v[1])&&$v[1] ? trim($v[1]) : '';
            $_country = isset($v[2])&&$v[2] ? trim($v[2]) : '';
            $price = isset($v[3]) ? trim($v[3]) : '';
            if(!$bDate || !$sku || !$_country || !$price){
                $errors[$count] = '必填项为空';
                continue;
            }
            $country = strtoupper($_country);
            if(!array_key_exists($country,$countryArr)){
                $countryArr[$country] = SysCountry::getIdByCountry($country);
            }
            $countryId = $countryArr[$country];
            if(!$countryId){
                $errors[$count] = $_country.'不存在';
                continue;
            }
            if(!array_key_exists($countryId,$areaArr)){
                $areaArr[$countryId] = SysICountry::getAreaIdByCountryId($countryId);
            }
            $areaId = $areaArr[$countryId];
            if(!array_key_exists($sku,$skuArr)){
                $skuArr[$sku] = ProductSku::getValueByCode($sku,'id');
            }
            $pSkuId = $skuArr[$sku];
            if(!$pSkuId){
                $errors[$count] = $sku.'不存在';
                continue;
            }
            $key = $bDate.'-'.$areaId.'-'.$pSkuId;
            if(array_key_exists($key,$arr)){
                continue;
            }
            $price = round(abs($price),3);
            LogisticsBasicsT::query()->updateOrCreate(
                [
                    'b_date' => $bDate,
                    'area_id' => $areaId,
                    'psku_id' => $pSkuId,
                ],['price' => $price]
            );
        }
//        if(!empty($errors)){
//            RExcel::importResult($errors);
//        }
    }

    protected function upOrderFee(){
        $file = base_path('data.xlsx');
        $lines = RExcel::importSheet($file);
        $errors = [];
        unset($lines[0]);
        $count = 0;
        foreach($lines as $v){
            $count++;
            if(count($v) < 3)continue;
            $code = isset($v[0]) ? trim($v[0]) : '';
            $mp = isset($v[1])&&$v[1] ? trim($v[1]) : '';
            $status = isset($v[2])&&$v[2] ? trim($v[2]) : '';
            if(!$code || !$mp || !$status){
                $errors[$count] = '必填项为空';
                continue;
            }
            $supplier = Supplier::query()->where('code',$code)->first();
            if(!$supplier){
                $errors[$count] = '供应商ERROR';
                continue;
            }
            $supplier->main_product = $mp;
            $supplier->status = $status;
            $supplier->save();
        }
        if(!empty($errors)){
            RExcel::importResult($errors);
        }
    }
}
